﻿Imports System.Data.SqlClient
Imports System.Data

Partial Class str_pr
    Inherits System.Web.UI.Page
#Region "Funcitons"
    Sub ImportPO(PRNumber As String, SqlSource As SqlDataSource)
        'Session("PRNumber") = PRNumber
        If PRNumber <> "" Then
            'SearchFromNAV.Close()

            'Dim dvSql As DataView = DirectCast(SqlDataSource2.Select(DataSourceSelectArguments.Empty), DataView)
            Dim dvSql As DataView = DirectCast(SqlSource.Select(DataSourceSelectArguments.Empty), DataView)

            For Each drvSql As DataRowView In dvSql
                Dim SearchDuplicateSQL = New SqlConnection(ConfigurationManager.ConnectionStrings("Management_Info_SysConnectionString").ConnectionString)
                Dim SearchDuplicateCommand = New SqlCommand()
                SearchDuplicateCommand.CommandText = "SELECT * FROM [Management_Info_Sys].[dbo].[pr_rcpt] WHERE [po_description] = @No_ and extra1 = @company"
                SearchDuplicateCommand.Parameters.AddWithValue("No_", drvSql("No_").ToString)
                SearchDuplicateCommand.Parameters.AddWithValue("company", "KR")
                SearchDuplicateCommand.Connection = SearchDuplicateSQL
                SearchDuplicateSQL.Open()
                Dim record As Integer = 0
                record = SearchDuplicateCommand.ExecuteScalar()
                SearchDuplicateSQL.Close()
                If record <= 0 Then
                    'Import
                    Dim ImportSql = New SqlConnection(ConfigurationManager.ConnectionStrings("Management_Info_SysConnectionString").ConnectionString)
                    Dim ImportCommand = New SqlCommand()
                    ImportCommand.CommandText = "INSERT INTO [dbo].[pr_rcpt] ([po_description],[vendor],[costcenter],[extrended_desc],[purchaser],[amount],[order_date],[extra1],[extra2])  " & _
                        "VALUES (@po_description,@vendor,@costcenter,@extrended_desc,@purchaser,@amount,@order_date,@extra1,@extra2)"
                    ImportCommand.Parameters.AddWithValue("po_description", drvSql("No_").ToString)
                    ImportCommand.Parameters.AddWithValue("vendor", drvSql("column1").ToString)
                    ImportCommand.Parameters.AddWithValue("costcenter", drvSql("Shortcut_Dimension_1_Code").ToString)
                    ImportCommand.Parameters.AddWithValue("extrended_desc", drvSql("Extended_Description").ToString)
                    ImportCommand.Parameters.AddWithValue("purchaser", drvSql("Purchaser_Code").ToString)
                    ImportCommand.Parameters.AddWithValue("amount", "0")
                    ImportCommand.Parameters.AddWithValue("order_date", drvSql("Order_Date").ToString)
                    ImportCommand.Parameters.AddWithValue("extra1", "KR")
                    ImportCommand.Parameters.AddWithValue("extra2", "")
                    ImportCommand.Connection = ImportSql
                    ImportSql.Open()
                    ImportCommand.ExecuteNonQuery()
                    ImportSql.Close()
                End If
            Next


        End If

    End Sub

    Sub ImportPOItems(PONumber As String, SqlSource As SqlDataSource)
        Session("PONumber") = PONumber
        If PONumber <> "" Then
            Dim dvSql As DataView = DirectCast(SqlSource.Select(DataSourceSelectArguments.Empty), DataView)

            For Each drvSql As DataRowView In dvSql
                Dim SearchDuplicateSQL = New SqlConnection(ConfigurationManager.ConnectionStrings("Management_Info_SysConnectionString").ConnectionString)
                Dim SearchDuplicateCommand = New SqlCommand()
                SearchDuplicateCommand.CommandText = "SELECT * FROM [Management_Info_Sys].[dbo].[po_items] WHERE [No_] = @No_ and extra1 = @company and extra2 = @linenumber"
                SearchDuplicateCommand.Parameters.AddWithValue("No_", drvSql("Document_No_").ToString)
                SearchDuplicateCommand.Parameters.AddWithValue("company", "KR")
                SearchDuplicateCommand.Parameters.AddWithValue("linenumber", drvSql("Line_No_").ToString)
                SearchDuplicateCommand.Connection = SearchDuplicateSQL
                SearchDuplicateSQL.Open()
                Dim record As Integer = 0
                record = SearchDuplicateCommand.ExecuteScalar()
                SearchDuplicateSQL.Close()
                If record <= 0 Then
                    'Import
                    Dim ImportSql = New SqlConnection(ConfigurationManager.ConnectionStrings("Management_Info_SysConnectionString").ConnectionString)
                    Dim ImportCommand = New SqlCommand()

                    ImportCommand.CommandText = "INSERT INTO [dbo].[po_items] ([TimeStamp],[Type],[No_],[Description],[UnitOfMeas],[PricePU],[Amount],[extra1],[extra2],[Quantity])  " & _
                        "VALUES (@TimeStamp,@Type,@No_,@Description,@UnitOfMeas,@PricePU,@Amount,@extra1,@extra2,@Quantity)"
                    ImportCommand.Parameters.AddWithValue("TimeStamp", "")
                    ImportCommand.Parameters.AddWithValue("Type", drvSql("Type").ToString)
                    ImportCommand.Parameters.AddWithValue("No_", drvSql("Document_No_").ToString)
                    ImportCommand.Parameters.AddWithValue("Description", drvSql("Description").ToString)
                    ImportCommand.Parameters.AddWithValue("UnitOfMeas", drvSql("Unit_of_Measure").ToString)
                    ImportCommand.Parameters.AddWithValue("PricePU", drvSql("Direct_Unit_Cost").ToString)
                    ImportCommand.Parameters.AddWithValue("Amount", drvSql("Amount").ToString)
                    ImportCommand.Parameters.AddWithValue("extra1", "KR")
                    ImportCommand.Parameters.AddWithValue("extra2", drvSql("Line_No_").ToString)
                    ImportCommand.Parameters.AddWithValue("Quantity", drvSql("Quantity_").ToString)
                    ImportCommand.Connection = ImportSql
                    ImportSql.Open()
                    ImportCommand.ExecuteNonQuery()
                    ImportSql.Close()
                End If
            Next
        End If
    End Sub

    Private Sub ResetForm()
        txtPrNumber.Text = ""
        txtrequisitionName.Text = ""
        txtApprovedBy.Text = ""
        txtComment.Text = ""
    End Sub


    Function POList(SqlSource As SqlDataSource) As ArrayList
        Dim ReturnList As New ArrayList
        Dim dvSql As DataView = DirectCast(SqlSource.Select(DataSourceSelectArguments.Empty), DataView)
        For Each drvSql As DataRowView In dvSql
            ReturnList.Add(drvSql("po_description").ToString())
        Next
        Return ReturnList
    End Function

#End Region

    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        Session("IdentityName") = Context.User.Identity.Name.ToString()
    End Sub

    Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        If txtPrNumber.Text = "" Then
            Exit Sub
        End If
        If txtrequisitionName.Text = "" Then
            Exit Sub
        End If
        If txtApprovedBy.Text = "" Then
            Exit Sub
        End If

        If ddlCompany.SelectedValue = "Select Company" Then
            Dim err As New CustomValidator
            err.IsValid = False
            err.ErrorMessage = "* Invalid Company"
            Page.Validators.Add(err)
            Return
        End If





        Try
            Session("TimeStamp") = Now.ToString

            'Check Duplicate

            Dim dupcon = New SqlConnection(ConfigurationManager.ConnectionStrings("Management_Info_SysConnectionString").ConnectionString)
            Dim dup = New SqlCommand()
            dup.CommandText = "Select [prid] from [Management_Info_Sys].[dbo].[pr_header] where [PrNumber] = '" & txtPrNumber.Text & "' and [Company] = '" & ddlCompany.SelectedValue & "'"
            dup.Connection = dupcon
            dupcon.Open()

            Dim record As Integer = 0
            record = dup.ExecuteScalar()
            dupcon.Close()

            If record > 0 Then
                Dim err As New CustomValidator
                err.CssClass = "ErrorLogs"
                err.IsValid = False
                err.ErrorMessage = "PR Number " & txtPrNumber.Text & " to " & ddlCompany.SelectedValue & " Already Exist!"
                Page.Validators.Add(err)

                Response.StatusCode = 201
                Exit Sub
            End If



            SqlDataSource2.Insert()
            'Check Items to NAV
            'PO
            ImportPO(txtPrNumber.Text, SqlDataSource3)
            Dim POHeaders As ArrayList = POList(GetPODataSource)
            Response.Write(POHeaders.Count)

            For Each item As String In POHeaders
                ImportPOItems(item, POSqlDataSource)
            Next
            'LINES


            ResetForm()

            lblLogs.CssClass = "SuccessLogs"
            lblLogs.Text = "Success"
            lblLogs.Visible = True
        Catch ex As Exception
            Dim err As New CustomValidator
            err.CssClass = "ErrorLogs"
            err.IsValid = False
            err.ErrorMessage = ex.Message & ex.ToString
            Page.Validators.Add(err)
        End Try


    End Sub


End Class
